﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;
namespace DAO
{
    public class PhieuNhapDAO
    {
        // bao cao sach Hoang THong
        public DataTable LayTableTheoSachThang(string Month)

        {
            string sql = " SELECT  ctp.masach,s.tensach , sum(convert(INT,ctp.SoLuongNhap)) as nhaphangthang ";
            sql += " from ChiTietPhieu ctp,PhieuNhap pn,sach s";
            sql += " where month(pn.NgayNhap) = '"+Month+"' and ";
            sql += " ctp.MaPhieuNhap = pn.MaPhieuNhap ";
            sql += " and  ctp.MaSach  = s.MaSach ";
            sql += " group by ctp.MaSach,s.tensach" ;
            
            
            SqlConnection con = DataProvider.ConnectDB();
            con.Open();
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataAdapter dp = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            dp.Fill(dt);
            return dt;

        }

        public DataTable LaySoLuongSachBanTrong1Thang(string m)
        {
            SqlConnection con = DataProvider.ConnectDB();
            //String sql = " SELECT     Sach.MaSach,Sach.TenSach, SUM(ChiTietPhieu.SoLuongNhap) AS nhaphangthang, SUM(ChiTietHoaDon.SoLuong) AS banhangthang FROM         Sach INNER JOIN  ChiTietHoaDon INNER JOIN  HoaDon ON ChiTietHoaDon.MaHoaDon = HoaDon.MaHoaDOn ON Sach.MaSach = ChiTietHoaDon.MaSach CROSS JOIN  PhieuNhap INNER JOIN  ChiTietPhieu ON PhieuNhap.MaPhieuNhap = ChiTietPhieu.MaPhieuNhap GROUP BY Sach.MaSach, Sach.TenSach ";            
            String sql = "SELECT   Sach.MaSach,sach.tensach,sach.soluong  ,  SUM(ChiTietHoaDon.SoLuong) AS banhangthang ";
                    sql += " FROM Sach INNER JOIN ";
                    sql += " ChiTietHoaDon INNER JOIN ";
                    sql += "  HoaDon ON ChiTietHoaDon.MaHoaDon = HoaDon.MaHoaDOn ON Sach.MaSach = ChiTietHoaDon.MaSach ";
                    sql += " where  month(hoadon.ngaylap) = '"+m+"'";
                    sql += " GROUP BY Sach.MaSach,sach.soluong,sach.tensach ";
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataAdapter ad = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            ad.Fill(dt);
            return dt;
                    
        }

        // bao cao sach by Hoang THong -- kết thúc
        public DataTable LayTableNguoc()
        {
            String sql = "SELECT top 1 * from PhieuNhap order by MaPhieuNhap desc";
            SqlConnection con = DataProvider.ConnectDB();
            con.Open();
            SqlCommand command = new SqlCommand(sql, con);

            SqlDataAdapter adapter = new SqlDataAdapter(command);

            DataTable dt = new DataTable();
            adapter.Fill(dt);

            con.Close();
            return dt;
        }
        public DataTable LayTable()
        {
            String sql = "SELECT * from PhieuNhap ";
            SqlConnection con = DataProvider.ConnectDB();
            con.Open();
            SqlCommand command = new SqlCommand(sql, con);

            SqlDataAdapter adapter = new SqlDataAdapter(command);

            DataTable dt = new DataTable();
            adapter.Fill(dt);

            con.Close();
            return dt;
        }
        public DataTable LayTableTheoNgay(DateTime form ,DateTime to)
        {
            String sql = "SELECT * from PhieuNhap where NgayNhap >= '"+form +"' and NgayNhap <= '"+ to+ "'";
            SqlConnection con = DataProvider.ConnectDB();
            con.Open();
            SqlCommand command = new SqlCommand(sql, con);

            SqlDataAdapter adapter = new SqlDataAdapter(command);

            DataTable dt = new DataTable();
            adapter.Fill(dt);

            con.Close();
            return dt;
        }
        public void them(PhieuNhapDTO pn)
        {
            String sql = "Insert into PhieuNhap(NgayNhap) values('" + pn.NgayNhap + "')";
           
            SqlConnection con = DataProvider.ConnectDB();
            con.Open();
            SqlCommand command = new SqlCommand(sql, con);
            int res = -1;
            res = command.ExecuteNonQuery();
            con.Close();
        }

        public void sua(PhieuNhapDTO pn)
        {
            String sql = "UPDATE PhieuNhap SET NgayNhap='" + pn.NgayNhap + "' where MaPhieuNhap =" + pn.MaPN;
            SqlConnection con = DataProvider.ConnectDB();
            con.Open();
            SqlCommand command = new SqlCommand(sql, con);
            int res = -1;
            res = command.ExecuteNonQuery();
            con.Close();
        }
        public void Xoa(PhieuNhapDTO pn)
        {
            String sql = "DELETE PhieuNhap where MaPhieuNhap =" + pn.MaPN;
            SqlConnection con = DataProvider.ConnectDB();
            con.Open();
            SqlCommand command = new SqlCommand(sql, con);
            int res = -1;
            res = command.ExecuteNonQuery();
            con.Close();
        }
    }
}
